<?php
namespace frontend\controllers;

use Yii;
use PDO;
use yii\web\Controller;
use yii\web\Session;

class TablesController extends Controller
{

	public $enableCsrfValidation = false;
	/*
	 * ½ûÓÃyiiÑùÊ½
	 */
	public $layout = false;
	/**
	 * 公共的pdo链接方法
	 * @param  [type] $sql [description]
	 * @return [type]      [description]
	 */
	public function pdo()
	{
		$k = Yii::$app->session->get('link_k'); 
		$dbname = Yii::$app->session->get('dbname'); 
        $dbinfos = Yii::$app->session->get('dbinfos');
        $dbinfos_arr = unserialize($dbinfos);
        $one_dbinfo = $dbinfos_arr[$k];
        $dbh = new PDO("mysql:host=$one_dbinfo[1];port=$one_dbinfo[2];dbname=$dbname;charset=utf8", "$one_dbinfo[3]", "$one_dbinfo[4]");
        return $dbh;
	}
	/*
	 * 
	 */
	public function actionTable_basic(){

		return $this->render('table_basic');
	}

	/**
	 * Õ¹Ê¾±íÃû
	 */
	public function actionTable_structure(){
		return $this->render('table_structure');
	}


	/**
	 * 删除表
	 */
	public function actionDel_table(){
		$dbh = $this->pdo();
        $tablenames = Yii::$app->request->post('tablenames'); //ÒªÉ¾³ýµÄ±íÃû³Æ
        $del_tablename_sql = "drop table $tablenames";
        $deltable = $dbh->exec($del_tablename_sql);
        if($deltable === 0){
            return json_encode('1');die;
        }
	}
	/**
	 * 表数据显示
	 * x
	 * @return [type] [description]
	 */
	public function actionShow_table()
	{
		$table = Yii::$app->request->get('table');
		Yii::$app->session->set('table',$table);
		$dbh = $this->pdo();
        $sql = "desc `$table`";
        $list = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        $sqls = "select count(*) as count from `$table`";
        $lists = $dbh->query($sqls)->fetchAll(PDO::FETCH_ASSOC);

		$arr1 = $lists[0]['count'];
        $size=10;
		$ye_shu=ceil($arr1/$size);
		$p=isset($_GET['p'])?$_GET['p']:1;
		$shang=$p-1<1?1:$p-1;
		$xia=$p+1>$ye_shu?$ye_shu:$p+1;
		$limit=($p-1)*$size;
		// ²éÑ¯Óï¾ä  
		$sql = "SELECT *  FROM  `$table` limit $limit,$size";  
		$data = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        return $this->render('show_list',['data'=>$data,'list'=>$list,'shang'=>$shang,'ye_shu'=>$ye_shu,'xia'=>$xia,'p'=>$p,'table'=>$table]);
	}
	/**
	 * 表数据添加后跳转的页面
	 * @return [type] [description]
	 */
	public function actionShow_tables()
	{
		$table = Yii::$app->session->get('table');
		$dbh = $this->pdo();
        $sql = "desc `$table`";
        $list = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        $sqls = "select count(*) as count from `$table`";
        $lists = $dbh->query($sqls)->fetchAll(PDO::FETCH_ASSOC);

		$arr1 = $lists[0]['count'];
        $size=10;
		$ye_shu=ceil($arr1/$size);
		$p=isset($_GET['p'])?$_GET['p']:1;
		$shang=$p-1<1?1:$p-1;
		$xia=$p+1>$ye_shu?$ye_shu:$p+1;
		$limit=($p-1)*$size;
		// ²éÑ¯Óï¾ä  
		$sql = "SELECT *  FROM  `$table` limit $limit,$size";  
		$data = $dbh->query($sql)->fetchAll(PDO::FETCH_ASSOC);
        return $this->render('show_list',['data'=>$data,'list'=>$list,'shang'=>$shang,'ye_shu'=>$ye_shu,'xia'=>$xia,'p'=>$p,'table'=>$table]);
	}
	/**
	 * 添加单条数据
	 * @return [type] [description]
	 */
	public function actionInsertone()
	{
		$data = Yii::$app->request->post('list');
		$pdo = $this->pdo();
		$table = Yii::$app->session->get('table');
		$sql = "desc `$table`";
		$list = $pdo->query($sql)->fetchAll(PDO::FETCH_ASSOC);
		$lists = array();
		foreach ($list as $k => $v) {
			if($k!=0){
				$lists[$k] = $v['Field'];
			}
		}
		$field = implode(',', $lists);
		$str = "'";
		foreach ($data as $key => $value) {
			$str .= $value;
			$str .= "','";
		}
		$datas = substr($str,0,-2);
		$sqls = "insert into $table($field) value($datas)";
		$pdo->exec($sqls);
		return $this->redirect(['tables/show_tables']);
	}
	/**
	 * 删除单条
	 * @return [type] [description]
	 */
	public function actionDeleteone()
	{
		$id = Yii::$app->request->post('id');
		$field = Yii::$app->request->post('field');
		$pdo = $this->pdo();
		$table = Yii::$app->session->get('table');
		$sql = "delete from $table where $field = '$id'";
		$res = $pdo->exec($sql);
		return $res;
	}
	/**
	 * 开启事物
	 * @return [type] [description]
	 */
	public function actionObject()
	{
		$pdo = $this->pdo();
		$sql = "begin";
		$res = $pdo->exec($sql);
		return $res;
	}
	/**
	 * 确定事物
	 * @return [type] [description]
	 */
	public function actionCommitobject()
	{
		$pdo = $this->pdo();
		$sql = "commit";
		$res = $pdo->exec($sql);
		return $res;
	}
	/**
	 * 事物回滚
	 * @return [type] [description]
	 */
	public function actionRollbackobject()
	{
		$pdo = $this->pdo();
		$sql = "rollback";
		$res = $pdo->exec($sql);
		return $res;
	}
	/**
	 * Çå¿Õ±íÊý¾Ý
	 * x
	 * @return [type] [description]
	 */
	public function actionEmpty_table()
	{
		$dbh = $this->pdo();
		$table = Yii::$app->session->get('table');
        $sql = "truncate table $table";
        $asd = $dbh->exec($sql);
        return $asd;
	}
	/**
	 * 创建表
	 * @return [type] [description]
	 */
	public function actionCreate_table(){
		if(yii::$app->request->ispost)
		{
			$data = yii::$app->request->post();
			// print_r($data);die;
			$pdo = $this->pdo();
			$str = '';
			$str .= 'create table `'.$data['table'].'`	(';
			foreach($data['field_name'] as $k=>$val)
			{
				// $str .= '<br>';
				$str .= "$val	";
				$str .= $data['field_type'][$k];
				$str .= '(';
				$str .=	$data['field_length'][$k];
				$str .= ')	';
				// ·ûºÅ
				if($data['field_attribute'][$k] != null)
				{
					$str .= 'unsigned	';
				}
				// Îª¿Õ
				if(isset($data['not_null'][$k]))
				{
					$str .= 'NOT NULL	';
				}
				// ×ÔÔö
				if(isset($data['field_extra'][$k]))
				{
					$str .= 'AUTO_INCREMENT	';
				}
				// 默认值
				if($data['field_default_value'][$k] == 2 && $data['default_value'][$k] != NULL)
				{
					$str .= 'default ';
					$str .= $data['default_value'][$k];
				}
				if($data['field_default_value'][$k] == 3)
				{
					$str .= 'default NULL';
				}
				if($data['field_default_value'][$k] == 4)
				{
					$str .= "default ''";
				}
				// ×¢ÊÍ
				// ×ÔÔö
				if(isset($data['field_comments'][$k]))
				{
					$str .= '	COMMENT ';
					$str .= "'";
					$str .= $data['field_comments'][$k];
					$str .= "'";
				}
				$str .= ',';
			}
			if($data['field_key'][0] == 'primary_1')
			{
				$str .= '	PRIMARY KEY (';
				$str .= $data['field_name'][0];
				$str .= ')';
			}
			$str .= ')';
			$str .= '	ENGINE=';
			$str .= $data['tbl_type'];
			$str .= '	CHARSET=utf8;';
			$count = $pdo->exec($str);
			$tmp= $pdo->errorInfo();
			if($tmp[0]==00000)
			{
				$at_dbname = Yii::$app->session->get('dbname');
				$all_tablename =  "SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '$at_dbname'";
				$tablenames = $pdo->query($all_tablename)->fetchAll(PDO::FETCH_ASSOC);
				// print_r($tablenames);die;
				return $this->render('table_show', ['table'  => $tablenames]);
			}
			else
			{
				print_r($tmp);die;
			}
		}
		return $this->render('create_table');
	}

}